{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "view-in-github"
   },
   "source": [
    "<a href=\"https://colab.research.google.com/github/tomasonjo/blogs/blob/master/Game_of_thrones_community_iteration/Game%20of%20thrones%20community%20iteration.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "_2CaCA2vDGbC"
   },
   "source": [
    "* Updated to GDS 2.3 version\n",
    "* Link to original blog post: https://towardsdatascience.com/community-detection-through-time-using-seed-property-in-neo4j-on-the-game-of-thrones-dataset-a2e520a6c79f"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "PG1voNyVDIfn",
    "outputId": "ba912efc-e226-4e11-b840-a5959c3f7435"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: neo4j in /home/tomaz/.local/lib/python3.8/site-packages (4.4.3)\r\n",
      "Requirement already satisfied: pytz in /home/tomaz/anaconda3/lib/python3.8/site-packages (from neo4j) (2021.1)\r\n"
     ]
    }
   ],
   "source": [
    "!pip install neo4j"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "id": "gOf9wc5_CvHx"
   },
   "outputs": [],
   "source": [
    "from neo4j import GraphDatabase\n",
    "host = 'bolt://3.231.25.240:7687'\n",
    "user = 'neo4j'\n",
    "password = 'hatchets-visitor-axes'\n",
    "driver = GraphDatabase.driver(host,auth=(user, password))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "id": "GZG8ZuroCvH1"
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "def run_query(query, params={}):\n",
    "    with driver.session() as session:\n",
    "        result = session.run(query, params)\n",
    "        return pd.DataFrame([r.values() for r in result], columns=result.keys())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "id": "TfpDkC2aCvH2"
   },
   "outputs": [],
   "source": [
    "from IPython.display import IFrame, HTML\n",
    "import json\n",
    "import uuid\n",
    "\n",
    "\n",
    "def generate_vis(host, user, password, cypher, labels_json, relationships_json):\n",
    "    html = \"\"\"\\\n",
    "    <html>\n",
    "    <head>\n",
    "    <title>Neovis.js Simple Example</title>\n",
    "            <style type=\"text/css\">\n",
    "                html, body {{\n",
    "                    font: 16pt arial;\n",
    "                }}\n",
    "                #viz {{\n",
    "                    width: 600px;\n",
    "                    height: 800px;\n",
    "                    font: 22pt arial;\n",
    "                }}\n",
    "            </style>\n",
    "            <script src=\"https://unpkg.com/neovis.js@2.0.2\"></script>\n",
    "            <script\n",
    "                    src=\"https://code.jquery.com/jquery-3.2.1.min.js\"\n",
    "                    integrity=\"sha256-hwg4gsxgFZhOsEEamdOYGBf13FyQuiTwlAQgxVSNgt4=\"\n",
    "                    crossorigin=\"anonymous\"></script>\n",
    "            <script type=\"text/javascript\">\n",
    "\n",
    "                let neoViz;\n",
    "\n",
    "                function draw() {{\n",
    "                    const config = {{\n",
    "                        containerId: \"viz\",\n",
    "                        neo4j: {{\n",
    "                            serverUrl: \"{host}\",\n",
    "                            serverUser: \"{user}\",\n",
    "                            serverPassword: \"{password}\",\n",
    "                        }},\n",
    "                        labels: {labels},\n",
    "                        relationships: {relationships},\n",
    "                        initialCypher: \"{cypher}\"\n",
    "                    }};\n",
    "\n",
    "                    neoViz = new NeoVis.default(config);\n",
    "                    neoViz.render();\n",
    "                }}\n",
    "            </script>\n",
    "         </head>\n",
    "        <body onload=\"draw()\">\n",
    "            <div id=\"viz\"></div>\n",
    "        </body>\n",
    "    </html>\n",
    "    \"\"\"\n",
    "\n",
    "    html = html.format(\n",
    "        host=host,\n",
    "        user=user,\n",
    "        password=password,\n",
    "        cypher=cypher,\n",
    "        labels = json.dumps(labels_json),\n",
    "        relationships=json.dumps(relationships_json)\n",
    "    )\n",
    "\n",
    "    unique_id = str(uuid.uuid4())\n",
    "    filename = \"graph-{}.html\".format(unique_id)\n",
    "\n",
    "    with open(filename, \"w\") as f:\n",
    "        f.write(html)\n",
    "    return IFrame(src=filename, width=1000, height=800)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "id": "7PBPz29RCvH3"
   },
   "outputs": [],
   "source": [
    "def visualize_level(level, community):\n",
    "    # Define cypher query\n",
    "    if level > 1:\n",
    "        cypher = \"\"\"MATCH (p1:Person)-[r:INTERACTS_{rel_level}|INTERACTS_{prev_level}]-(p2:Person) \\\n",
    "                    WHERE p1.community_{level} = {community} RETURN *\"\"\".format(\n",
    "            rel_level=level if level != 4 else 45,level=level, prev_level=level -1, community=community)\n",
    "    else:\n",
    "        cypher = \"\"\"MATCH (p1:Person)-[r:INTERACTS_{level}]-(p2:Person) \\\n",
    "                    WHERE p1.community_{level} = {community} RETURN *\"\"\".format(level=level, community=community)\n",
    "    print(cypher)\n",
    "    # Define relationships_json\n",
    "    relationships_json = dict()\n",
    "    for l in [level-1,level]:\n",
    "        relationships_json[\"INTERACTS_{}\".format(l if l != 4 else 45)] = {\n",
    "                \"caption\": False\n",
    "            }\n",
    "    # Define labels_json    \n",
    "    labels_json = {\n",
    "        \"Person\": {\n",
    "            \"label\": \"id\",\n",
    "            \"group\": \"community_{}\".format(level)\n",
    "        }\n",
    "    }\n",
    "\n",
    "    return generate_vis(host, user, password, cypher, labels_json, relationships_json)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "SjP8vjfWCvH4"
   },
   "source": [
    "# Import"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 49
    },
    "id": "udyBibdJCvH5",
    "outputId": "0cc93822-b5ac-4cc4-e60b-b1689ccfa7bd"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "constraint_query = \"\"\"CREATE CONSTRAINT IF NOT EXISTS FOR (p:Person) REQUIRE p.id IS UNIQUE;\"\"\"\n",
    "run_query(constraint_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "id": "DEcCdTlNCvH6"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>'done'</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>done</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  'done'\n",
       "0   done"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# https://networkofthrones.wordpress.com/\n",
    "import_networks = \"\"\"\n",
    "\n",
    "UNWIND ['1','2','3','45'] as book\n",
    "LOAD CSV WITH HEADERS FROM \n",
    "'https://raw.githubusercontent.com/mathbeveridge/asoiaf/master/data/asoiaf-book' + book + '-edges.csv' as value\n",
    "MERGE (source:Person{id:value.Source})\n",
    "MERGE (target:Person{id:value.Target})\n",
    "WITH source,target,value.weight as weight,book\n",
    "CALL apoc.merge.relationship(source,'INTERACTS_' + book, {}, {weight:toFloat(weight)}, target) YIELD rel\n",
    "RETURN distinct 'done'\n",
    "\n",
    "\"\"\"\n",
    "run_query(import_networks)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "id": "g4-CauAHEIIo"
   },
   "outputs": [],
   "source": [
    "def write_louvain(book):\n",
    "  project_graph_query = f\"\"\"\n",
    "   CALL gds.graph.project.cypher('book',\n",
    "  'MATCH (p:Person)\n",
    "  WHERE (p)-[:INTERACTS_{book}]-()\n",
    "  RETURN id(p) as id',\n",
    "  'MATCH (p:Person)-[:INTERACTS_{book}]-(p1:Person)\n",
    "  RETURN id(p) as source, id(p1) as target')\n",
    "\"\"\"\n",
    "\n",
    "  louvain_book = f\"\"\"\n",
    "  CALL gds.louvain.write('book'\n",
    "  ,{{writeProperty:'community_{book}'}})\n",
    "  \"\"\"\n",
    "\n",
    "  drop_graph = \"\"\"\n",
    "  CALL gds.graph.drop('book')\n",
    "  \"\"\"\n",
    "  run_query(project_graph_query)\n",
    "  run_query(louvain_book)\n",
    "  run_query(drop_graph)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "rtmxB-ytCvH7"
   },
   "source": [
    "# Book 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "id": "ZTtOdPv-CvH8"
   },
   "outputs": [],
   "source": [
    "write_louvain(\"1\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "id": "Hr2r-5U1CvH8"
   },
   "outputs": [],
   "source": [
    "# Get Daenerys' community id \n",
    "get_daenerys_community_query = \"\"\"\n",
    "MATCH (p:Person{id:'Daenerys-Targaryen'})\n",
    "RETURN p.community_1 as community\n",
    "\"\"\"\n",
    "\n",
    "daenerys_community = run_query(get_daenerys_community_query)['community'][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 839
    },
    "id": "jTi8TtCUCvH9",
    "outputId": "2d6073df-7ed1-45e5-be44-2369660652cd"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MATCH (p1:Person)-[r:INTERACTS_1]-(p2:Person)                     WHERE p1.community_1 = 52 RETURN *\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "        <iframe\n",
       "            width=\"1000\"\n",
       "            height=\"800\"\n",
       "            src=\"graph-4ac2b810-aad7-4cf9-b8f6-3de7847ed870.html\"\n",
       "            frameborder=\"0\"\n",
       "            allowfullscreen\n",
       "        ></iframe>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.lib.display.IFrame at 0x7f9026731100>"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "visualize_level(level=1,community=daenerys_community)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "Ye6-xWwdCvH-"
   },
   "source": [
    "# Book 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "id": "SkCJduILCvH-"
   },
   "outputs": [],
   "source": [
    "write_louvain(\"2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "id": "nTNS2AK1CvH-",
    "outputId": "b155bc72-1b22-4609-b915-c34314c21408"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MATCH (p1:Person)-[r:INTERACTS_2|INTERACTS_1]-(p2:Person)                     WHERE p1.community_2 = 52 RETURN *\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "        <iframe\n",
       "            width=\"1000\"\n",
       "            height=\"800\"\n",
       "            src=\"graph-81e6b2c4-b490-423e-a6c5-b4d737928199.html\"\n",
       "            frameborder=\"0\"\n",
       "            allowfullscreen\n",
       "        ></iframe>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.lib.display.IFrame at 0x7f9026731b50>"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "visualize_level(level=2,community=daenerys_community)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "niEQO5pwCvH_"
   },
   "source": [
    "# Book 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "id": "MIITSfdACvH_"
   },
   "outputs": [],
   "source": [
    "write_louvain(\"3\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "id": "K0GJcsFqCvH_",
    "outputId": "36d8e774-d0ec-4a4d-f9f0-898f867c851f"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MATCH (p1:Person)-[r:INTERACTS_3|INTERACTS_2]-(p2:Person)                     WHERE p1.community_3 = 52 RETURN *\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "        <iframe\n",
       "            width=\"1000\"\n",
       "            height=\"800\"\n",
       "            src=\"graph-53911c3f-c2a1-43fe-bc75-b5591f6b5abd.html\"\n",
       "            frameborder=\"0\"\n",
       "            allowfullscreen\n",
       "        ></iframe>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.lib.display.IFrame at 0x7f90267318b0>"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "visualize_level(level=3,community=daenerys_community)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "zCadSySZCvIA"
   },
   "source": [
    "# Book 4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "id": "B5wN4bd4CvIA"
   },
   "outputs": [],
   "source": [
    "write_louvain(\"45\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "id": "Tc4FVKVTCvIA",
    "outputId": "afc17880-1ac3-4428-83c5-fbe8eb332517"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MATCH (p1:Person)-[r:INTERACTS_45|INTERACTS_3]-(p2:Person)                     WHERE p1.community_4 = 52 RETURN *\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "        <iframe\n",
       "            width=\"1000\"\n",
       "            height=\"800\"\n",
       "            src=\"graph-8ccd18de-ea67-4007-b119-408a9129954f.html\"\n",
       "            frameborder=\"0\"\n",
       "            allowfullscreen\n",
       "        ></iframe>\n",
       "        "
      ],
      "text/plain": [
       "<IPython.lib.display.IFrame at 0x7f901a964220>"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "visualize_level(level=4,community=daenerys_community)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "uWD1_qV3CvIA"
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "include_colab_link": true,
   "name": "Game of thrones community iteration.ipynb",
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
